package occ.service
{
import com.greatwideweb.util.*;

import flash.data.*;
import flash.events.*;
import flash.events.EventDispatcher;
import flash.events.IEventDispatcher;

import mx.collections.ArrayCollection;
import mx.utils.ObjectUtil;

import occ.model.ReceiptVO;
import occ.model.ReservationVO;
import occ.model.ReserviewItemVO;
import occ.model.SiteTypeVO;
import occ.utils.*;

  public class ReservationService extends EventDispatcher
  {
  
    public var isAvailable:Boolean= false;
    public var reservationId:int;
    public var reservations:Array;
    
    public static const AVAILABLE_DATE:String="ReservationIsAvailableEvent";
    public static const UNAVAILABLE_DATE:String="ReservationIsNotAvailableEvent";
    public static const SAVE:String="ReservationSaveResult";
    public static const DELETE:String="ReservationServicedeleteResvationEvent";
    public static const ERROR:String="ReservationError";
    public static const GET_RESERVATION_LIST:String="GetreservaitonList";
    
    
    private var sqlData:ArrayCollection;
		private var stmt:SQLStatement;
		private var sql:String;
    private var r:ReserviewItemVO;
    
    public function isReservationDatesAvailable(siteId:int, startDate:Date, endDate:Date):void
    {
      trace("isReservationDatesAvailable");
      var fmtStartDate:String = DateUtils.getSQLLiteDate(startDate);
      var fmtEndDate:String = DateUtils.getSQLLiteDate(endDate);
      trace("Dates: " + fmtStartDate + " and " + fmtEndDate);
      sql="select count(*) as cnt "+
          " from reservations  "+
          "  where season_id = 5 "+
          "    and site_id =  "+ siteId +
          "    and season_id =  "+ Season.seasonId +
          "    and active_flag ='Y' "+
          "    and ( "+
          "     strftime('%J', date('" + fmtStartDate + "')) "+
          "      between strftime('%J',start_date) "+
          "          and strftime('%J',end_date) "+
          "     or "+
          "     strftime('%J', date('" + fmtEndDate + "')) "+
          "      between strftime('%J',start_date) "+
          "          and strftime('%J',end_date) "+
            ")";
      trace("isReservationDatesAvailable");
      trace(sql);
      stmt = new SQLStatement();
			stmt.sqlConnection = DatabaseManager.getConnection();
			
			stmt.addEventListener( SQLErrorEvent.ERROR, onSQLError );
			stmt.addEventListener( SQLEvent.RESULT, onIsReservationDatesAvailableResult );
			stmt.text = sql;
			stmt.execute();
    }
    
    private function onIsReservationDatesAvailableResult(event:SQLEvent):void
    {
      isAvailable = false;
      trace("onIsReservationDatesAvailableResult");
      var result:SQLResult = stmt.getResult();
			sqlData = new ArrayCollection( result.data );
			for(var i:int=0;i<sqlData.length;i++){
        if((sqlData[i] as Object).cnt == "0")
          isAvailable = true;
        else
          isAvailable = false;
			  
			}
      trace("After the loop");
			 if(isAvailable){ 
         trace("AVAILABLE_DATE");
			   dispatchEvent(new Event(AVAILABLE_DATE));
       }else{
         trace("UNAVAILABLE_DATE");
         dispatchEvent(new Event(UNAVAILABLE_DATE));
       }
         
         
    }
    
    public function createReservation(r:ReservationVO):void
    {
      reservationId = r.reservationId;
      
      sql =" insert into reservations "+
          " (season_id,cust_id, site_id, "+
          " start_date, end_date, "+
          " total_guests, "+
          " est_price,tax, discount, actual_price, amount_paid) "+
          " values "+
          " (" + Season.seasonId + "," + r.customerData.custId  + ", " + r.siteData.siteId + 
          ", '" + DateUtils.getSQLLiteDate(r.startDate) + 
          "', '" + DateUtils.getSQLLiteDate(r.endDate) + "'," + r.guestCount + "," +
          r.receipt.baseAmount  + "," + r.receipt.taxAmount + ", " + 
          r.receipt.discount + ", " + r.receipt.totalAmount + ", " + r.amountCollected + ")";
      trace(sql);
      stmt = new SQLStatement();
			stmt.sqlConnection = DatabaseManager.getConnection();
			
			stmt.addEventListener( SQLErrorEvent.ERROR, onSQLError );
			stmt.addEventListener( SQLEvent.RESULT, onNewReservationResult );
			stmt.text = sql;
			stmt.execute();
    }
    
    private function onNewReservationResult(event:SQLEvent):void
    {
      sql="select last_insert_rowid() as result";
      stmt = new SQLStatement();
			stmt.sqlConnection = DatabaseManager.getConnection();
			
			stmt.addEventListener( SQLErrorEvent.ERROR, onSQLError );
			stmt.addEventListener( SQLEvent.RESULT, onReportSaveResult );
			stmt.text = sql;
			stmt.execute();
    }
    
    private function onReportSaveResult(event:SQLEvent):void
    {
      trace("onReportSaveResult");
       if(reservationId == ReservationVO.NULL_RESERVATION_ID){
         var result:SQLResult = stmt.getResult();
			   sqlData = new ArrayCollection( result.data );
			   for(var i:int=0;i<sqlData.length;i++){
  			   reservationId = (sqlData[i] as Object).result
  			 }
       }
       dispatchEvent(new Event(SAVE));
    }
    
    public function editReservation(r:ReservationVO):void
    {
      reservationId = r.reservationId;
      
      sql =
      " update reservations set "+
            "  cust_id = " + r.customerData.custId + ", "+
            "  site_id = " + r.siteData.siteId + ", "+
            "  start_date = '" + r.startDate + "', "+ 
            "  end_date='" + r.endDate + "',  "+
            "  est_price=" + r.receipt.baseAmount + ",  "+
            "  tax=" + r.receipt.taxAmount + ",  "+
            "  discount=" + r.receipt.discount + ", "+ 
            "  actual_price=" + r.receipt.totalAmount + ", "+ 
            "  amount_paid=" + r.amountCollected + ",  "+
            "  total_guests= " + r.guestCount +
            "  where reservation_id = " + r.reservationId;
      trace(sql);
      stmt = new SQLStatement();
			stmt.sqlConnection = DatabaseManager.getConnection();
			
			stmt.addEventListener( SQLErrorEvent.ERROR, onSQLError );
			stmt.addEventListener( SQLEvent.RESULT, onEditReservationResult );
			stmt.text = sql;
			stmt.execute();
    }
    
    private function onEditReservationResult(event:SQLEvent):void
    {
      dispatchEvent(new Event(SAVE));
    }
    
    public function deleteReservation(r:ReservationVO):void
    {
      reservationId = r.reservationId;
      
      sql ="";
      trace(sql);
      stmt = new SQLStatement();
			stmt.sqlConnection = DatabaseManager.getConnection();
			
			stmt.addEventListener( SQLErrorEvent.ERROR, onSQLError );
			stmt.addEventListener( SQLEvent.RESULT, onDeleteReservationResult );
			stmt.text = sql;
			stmt.execute();
    }
    
    private function onDeleteReservationResult(event:SQLEvent):void
    {
      dispatchEvent(new Event(DELETE));
    }
    
    private function onSQLError( event:SQLErrorEvent ):void
		{
			var errorString:String = "SQL Error: " + event.error.errorID + "nDetails: " + event.error.details + "nDetail Arguments: " +event.error.detailArguments;
 			throw new Error(errorString);
		}
    
    public function getReceipt(r:ReservationVO):ReceiptVO
    {
      return new ReceiptBuilder(r).receipt;
    }
    
    public function getReservationList(startDate:Date, endDate:Date):void{
    
      var fmtStartDate:String = DateUtils.getSQLLiteDate(startDate);
      var fmtEndDate:String = DateUtils.getSQLLiteDate(endDate);
      trace("fmtStartDate: " + fmtStartDate);
      trace("fmtEndDate: " + fmtEndDate);
      sql=" select c.cust_id, c.first_name || ' ' || c.last_name as name, "+
          "        r.reservation_id, r.actual_price,  site_id, "+
          "        start_date, end_date, r.amount_paid, "+
          "        strftime('%m',r.start_date) as start_date_mm, "+ 
          "         strftime('%d',r.start_date) as start_date_dd, "+
          "        strftime('%Y',r.start_date) as start_date_yyyy, "+
          "        strftime('%m',r.end_date) as end_date_mm, "+
          "        strftime('%d',r.end_date) as end_date_dd, "+
          "        strftime('%Y',r.end_date) as end_date_yyyy, "+
          "        round(r.actual_price - r.amount_paid, 2)balance "+
          "   from reservations r, customer c "+
          "  where r.season_id = "+ Season.seasonId +
          "    and r.cust_id = c.cust_id "+
          "    and ( "+
          "     strftime('%J',start_date) "+
          "      between strftime('%J', date('" + fmtStartDate + "')) "+
          "        and  strftime('%J', date('" + fmtEndDate + "')) "+
          "     or "+
          "     strftime('%J',end_date) "+
          "      between strftime('%J', date('" + fmtStartDate + "')) "+
          "        and  strftime('%J', date('" + fmtEndDate + "')) "+
          " ) ";
      trace("sql:  " + sql);
      stmt = new SQLStatement();
			stmt.sqlConnection = DatabaseManager.getConnection();
			stmt.addEventListener( SQLErrorEvent.ERROR, onSQLError );
			stmt.addEventListener( SQLEvent.RESULT, onGetReservationListResult );
			stmt.text = sql;
			stmt.execute();
      
    }
    
    public function onGetReservationListResult(event:SQLEvent):void{
    
     
      var result:SQLResult = stmt.getResult();
			sqlData = new ArrayCollection( result.data );
			for(var i:int=0;i<sqlData.length;i++){
         trace("are we hitting");
  			 r = new ReserviewItemVO();
         r.reservationId = (sqlData[i] as Object).reservation_id;
         r.startDate = new Date((sqlData[i] as Object).start_date_yyyy, (sqlData[i] as Object).start_date_mm, (sqlData[i] as Object).start_date_dd);
         r.endDate = new Date((sqlData[i] as Object).end_date_yyyy, (sqlData[i] as Object).end_date_mm, (sqlData[i] as Object).end_date_dd);
         r.isHeader = false;
         r.style ="reserved";
         r.siteId = (sqlData[i] as Object).site_id;
         r.toolTip ="Reserved by " + (sqlData[i] as Object).name;
         if(reservations == null)
            reservations = new Array();
         reservations.push(r);
  		}
      dispatchEvent(new Event(GET_RESERVATION_LIST));
    }
  }
}